qBase V1.21 Query for dBase, Clipper, and Foxbase .DBF Files Copyright 1990 Santa Clarita SoftWorks Santa Clarita, California 1. What is qBase? qBase was developed for quick and intuitive query reporting of existing dBase type .DBF files. The user can design, structure, and save a custom inquiry in a matter of seconds with a printed report generated as an option. There is no other demand placed upon the user other than supplying the .DBF files and possessing a minimal amount of database structuring knowledge. This program will not change the user's files in anyway except in qBase's edit mode. It will only read the original datafile, create temporary indexes, and delete those temporary indexes. It will require at least 512k of memory with minimal requirements for free disk space. qBase was developed and compiled with Clipper version Summer 1987, a Nantucket Corporation trademark. 2. CREATING THE QUERY First step in using qBase is to path or place QBASE.EXE in any directory. To execute qBase, key in QBASE screen similar to Figure 1 will appear. Given any existing dBase type file, qBase will detect the presence of that or any other .DBF file in the current directory and the query starts from that point. An alternate method is with a DBF filename (i.e. C:/>QBASE DRAWMAST). This will bypass first opening screen and proceed directly to REPORT fields window. FIGURE 1: Structure Query Menu ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» Shareware Version ºDRAWINGS.DBF º qBase V1.21 ºMASTER.DBF º Copyright 1990 ºMRP.DBF º Santa Clarita SoftWorks ºPRODUCT.DBF º PO Box 802664 ºPURCHASE.DBF º Santa Clarita CA 91380-2664 ºSCHEDULE.DBF º ºSTKROOM.DBF º ºTIMECARD.DBF º ºWORK.DBF º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ Use Cursor to Locate File, Enter to Query, ESC to Quit FIGURE 2: Query for dBase/Clipper/Foxbase Files Santa Clarita SoftWorks 1990 Select Report Fields ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º EMPLOYEE º º JOBNUMBER º º ORDER º º HOURS º º WEEK º º MONTH º º YEAR º º SYSDATE º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º Enter - Selectº º F5- Tags All º º F6- Untags Allº º º º Sort Menu ===>º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ After the first selection of the data file, a window will appear to the left of the first window asking if a second .DBF (Figure 12, 13) is desired (see Section 4). The default is (N)o, whereas if the operator inserts (Y)es, qBase will return to original .DBF file window minus the first chosen .DBF file. The relationship of two files (.DBFs) will be described in detail in Section 4 of this user guide. REPORT FIELDS The window title 'Report Fields' is the user's selection menu for what fields will appear in the final report. To select the desired fields hit the enter key while the field is highlighted by the light bar, or hit the F5 function key to mark all of the data fields for the final report. A checkmark will appear to the right of the selected reporting data fields. To access all of available fields in the user's file, scroll down or up with the cursor keys to view additional data fields. If you wish to alter the selection after marking, press the enter key while highlighting the checkmarked field and the field will be untagged and will not be presented in the final report. To unmark all of the fields, press the F6 function key to globally remove the checkmarks and return to a clean slate. After selection of the fields, press the right arrow cursor key to move to the next window for sort selection. During the qBase session, the SORT and CRITERIA parameters that you are setting for the query are being echoed to the top half of the screen. This message will give a string that will be dynamically changed when you make the change, and continued on to be printed on the final report. SORT FIELDS Figure 3 depicts how the screen should appear after the Report Field selection has been completed. The datafield list will appear as it did in Report Fields window. In relational data file mode, this list of fields will only reflect the second or base file. FIGURE 3: Select Report Fields Select Sort Fields ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º EMPLOYEE û º º EMPLOYEE º º JOBNUMBER û º º JOBNUMBER º º ORDER û º º ORDER º º HOURS û º º HOURS º º WEEK û º º WEEK º º MONTH û º º MONTH º º YEAR û º º YEAR º º SYSDATE û º º SYSDATE º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º Enter - Selectº º Order º º Erase - Enter º º Over Any Pickº º <=== Menu ===>º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ This window will allow the user to select what order the data will appear in the final query. A sort order is not a requirement for qBase and to by pass this window, use the right cursor key to move on to the CRITERIA selection menu. The light bar is used for selecting fields in sorting the final query. Use the Enter key to mark the first field in the sort order. Upon hitting the Enter key, a window in the lower left hand corner will appear asking for 'Ascending' or 'Descending' order. The default is 'Ascending', but if descending order is selected, qBase will return back to the SORT menu marking the selected SORT field with its numeric priority plus a small letter 'd' along side of it. In this example, a number '1' (EMPLOYEE) will appear to the right of the that field designating it as the first field in the ascending sort order. To add another field for the SORT order, hit the Enter key while highlighting the desired field and a number '2' (MONTH) will appear to the right. Continue to select fields for the sort order by the light bar and enter key until the final sort order has been determined and marked with a numeric character. To change the SORT order, simply place the light bar on a previously selected field and use the enter key to erase all of the numeric marks and reinitiate the SORT selection process. Finalized the SORT order by using the right cursor key to move into the Criteria selection menu. CRITERIA FIELD To construct the query, the third window as pictured in Figure 4 is used for selecting what field will be the 'subject' for criteria. Press the enter key on the a select field (EMPLOYEE) and arrowhead will appear to the right point to the field name. To change this 'subject' field, hit enter on the same field to blank out the arrowhead, or hit the enter key on another field for a new selection. There can only be one field selected at a time in this stage. FIGURE 4: TIMECARD.DBF is sorted on EMPLOYEE, MONTH Where Select Criteria Field ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º EMPLOYEE û º º EMPLOYEE 1 º º EMPLOYEE º º JOBNUMBER û º º JOBNUMBER º º JOBNUMBER º º ORDER û º º ORDER º º ORDER º º HOURS û º º HOURS º º HOURS º º WEEK û º º WEEK º º WEEK º º MONTH û º º MONTH 2 º º MONTH º º YEAR û º º YEAR º º YEAR º º SYSDATE û º º SYSDATE º º SYSDATE º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ºEnter - Select º º Query º ºF10 To Query º ºNONE for ALL º º<=== Sort Menu º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ If no query to a specific condition is required, hitting the F10 function key without marking any field will give a complete display and report of the database. Hitting the F10 function key with marked a field, a small window in the lower left hand corner of the screen will appear with two choices: 'IS' or 'IS NOT' (Figure 14). The default is 'IS'; and if 'IS NOT' is selected, all elements NOT matching the following CRITERIA will be reported. After selecting the IS/IS NOT qualifier, a fourth window will appear as shown in Figure 5. The fourth window is a menu for selecting logic operators. FIGURE 5: TIMECARD.DBF is sorted on EMPLOYEE, MONTH Where EMPLOYEE Select Criteria Field ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º EMPLOYEE û º º EMPLOYEE 1 º º EMPLOYEE < º º JOBNUMBER û º º JOBNUMBER º º JOBNUMBER º º ORDER û º º ORDER º º ORDER º º HOURS û º º HOURS º º HOURS º º WEEK û º º WEEK º º WEEK º º MONTH û º º MONTH 2 º º MONTH º º YEAR û º º YEAR º º YEAR º º SYSDATE û º º SYSDATE º º SYSDATE º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º Equal to Not Equal to º º Greater Than Between º º Less Than Sounds Like º º Greater Than/Equal Contains º º Less Than/Equal BLANKS or 0 º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ All Records Equal to (=) The logic condition commands are listed below require no explanation, but there are some noted features about qBase's use of them: Equal to - Will collect all records meeting criteria, and if in a character field, keying only a single character will identify all records beginning with that character. Blank entry will present all records. Greater Than - All records greater than and not including the subject. Less Than - All records less than and not including the subject. Greater Than/Equal - All records greater than and equal to the subject. Less Than/Equal - All records greater than and equal to the subject. Not Equal to - All records not exactly equal to the subject. Between - All records between a set of limits, and not including the two subjects. Sounds Like - All phonetic sound 'alikes' based on the Soundex algorithm. Contains - All records containing sub-string, either as a whole record equaling the sub-string or in part. Blanks or 0 - All blank records or containing zero (0). Select the operator by highlighting the logic command and hitting the enter key. A prompt will appear requesting the operator to input specific data (EMPLOYEE = CURREY) for the query. Upon completion of the data entry hit enter and a continuation window will appear on the left side of the screen (Figure 6) asking the user if additional parameters are to be added to the inquiry. qBase is case sensitive; in this example, Currey will have to be all capitals (CURREY) to access all of Currey's timecard records. Three choices are given: NO - No Further Conditions Required AND - If Both Conditions are True OR - If Either Condition is True Selecting either of the last two choices will continue the process of selecting a second condition for the query report. The entry process is the same for the second condition operator, finalized with the enter key (Figure 7). Selecting .AND. in this case with MONTH BETWEEN 03 AND 06 shows: FIGURE 6: TIMECARD.DBF is sorted on EMPLOYEE, MONTH Where EMPLOYEE is equal to CURREY Select Criteria Field ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º EMPLOYEE û º º EMPLOYEE 1 º º EMPLOYEE < º º JOBNUMBER û º º JOBNUMBER º º JOBNUMBER º º ORDER û º º ORDER º º ORDER º º HOURS û º º HOURS º º HOURS º º WEEK û º º WEEK º º WEEK º º MONTHÉÍÍÍÍÍÍÍÍÍÍÍÍ» º MONTH 2 º º MONTH º º YEAR ºAdditional º º YEAR º º YEAR º º SYSDAºCriteria? º º SYSDATE º º SYSDATE º ÈÍÍÍÍÍͺ NO º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ º .AND. º ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º .OR. º º Equal to Not Equal to º ÈÍÍÍÍÍÍÍÍÍÍÍͼ º Greater Than Between º º Less Than Sounds Like º Record Criteria? º Greater Than/Equal Contains º CURREY º Less Than/Equal BLANKS or 0 º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ No, Continue FIGURE 7: TIMECARD.DBF is sorted on EMPLOYEE, MONTH Where EMPLOYEE is equal to CURREY and MONTH Select Criteria Field ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º EMPLOYEE û º º EMPLOYEE 1 º º EMPLOYEE º º JOBNUMBER û º º JOBNUMBER º º JOBNUMBER º º ORDER û º º ORDER º º ORDER º º HOURS û º º HOURS º º HOURS º º WEEK û º º WEEK º º WEEK º º MONTH û º º MONTH 2 º º MONTH < º º YEAR û º º YEAR º º YEAR º º SYSDATE û º º SYSDATE º º SYSDATE º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º Equal to Not Equal to º º Greater Than Between º º Less Than Sounds Like º Lower Record Criteria? º Greater Than/Equal Contains º 03 º Less Than/Equal BLANKS or 0 º Upper Record Range? ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ 06 After each individual criteria selection, a window will appear asking if the prior criteria is a .NOT. condition, that is, all records that do not meet the selected condition will be listed in the query. qBase defaults to 'N' for rapid keying and will present the query as is. THE QUERY REPORT The sorting (if any) and condition search process occurs with a screen similar to Figure 8 appearing with final query result. This inquiry screen can be scrolled by using the cursor, Page Up/Down, Home, End, and Tab keys. At the bottom of the screen are listed hot keys for editing and printing data in the query. A display in the lower leftmost corner of the screen shows the number resulting records versus the total number of records in the starting base file. In this case, 9/700, or 9 records from a total of 700 records in TIMECARD.DBF. FIGURE 8: All Records in a Selected Range (||) ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º TIMECARD.DBF is sorted on EMPLOYEE, MONTH º º Where EMPLOYEE is equal to CURREY and MONTH is between 03 and 06 º º º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ EMPLOYEE JOBNUMBER ORDER HOURS WEEK MONTH YEAR ÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÑÍÍÍÍÍÍÑÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍ CURREY ³ 14555101 ³ BK108-000 ³ 35.00 ³ 14 ³ 04 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 40.00 ³ 21 ³ 04 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 36.00 ³ 07 ³ 04 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 10.00 ³ 28 ³ 04 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 12.00 ³ 05 ³ 05 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 14.00 ³ 31 ³ 05 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 29.00 ³ 26 ³ 05 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 22.00 ³ 12 ³ 05 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 35.00 ³ 19 ³ 05 ³ 89 ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ Query#/Total# = 9/700 F7 - Print Report ENTER - Edit ESC - Return F3 - Save Query (.QBA) F4 - Save Query (.DBF) F5 - Data Replace At this stage of the query, the user has options to keep, edit, report the resulting answer file. Editing To EDIT, hit the ENTER key while on a highlighted field. With color monitors the light bar will change from red-on-white to yellow-on-green, letting the user to modify the data. For Global editing, F5 will allow one field in all of the displayed records meeting the criteria be changed in mass. If the user wanted to change ORDER data from 'BK100-000' to 'AH212-111', first hit the F5 key, select the ORDER field in the window that appears, a second window will follow with the file/field to be changed and requesting the replacement data. Enter AH212-111 in the blank spot provided, hit enter. qBase will replace all field records with that change information and return to the inquiry screen. If a element in the criteria is changed such as CURREY, placing it outside of the query, then those records will disappear from view, but not from the file. The ESCape key can be used at any stage of the edit to abort any possible changes prior to the final window. Printing To PRINT the query report, function key F7 will pop a window print menu requesting optional printer commands (Figure 9). Default List and Menu Selection: Set Page Length: Default = 50 Margins: Default = Left: 05 Right: 80 Condensed Print Default = Off FIGURE 9: ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º TIMECARD.DBF is sorted on EMPLOYEE, MONTH º º Where EMPLOYEE is equal to CURREY and MONTH is between 03 and 06 º º º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ EMPLOYEE JOBNUMBER ORDER HOURS WEEK MONTH YEAR ÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÑÍÍÍÍÍÍÑÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍ CURREY ³ 14555101 ³ BK108-000 ³ 35.00 ³ 14 ³ 04 ³ 89 CURREY ³ 14555101 ³ BK108-000 ³ 40.00 ³ 21 ³ 04 ³ 89 CURREY ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ³ 36.00 ³ 07 ³ 04 ³ 89 CURREY ºShareware Ver. qBase V1.21 º ³ 10.00 ³ 28 ³ 04 ³ 89 CURREY º Copyright 1990 º ³ 12.00 ³ 05 ³ 05 ³ 89 CURREY º Santa Clarita SoftWorks º ³ 14.00 ³ 31 ³ 05 ³ 89 CURREY º ----------------------- º ³ 29.00 ³ 26 ³ 05 ³ 89 CURREY º * Print Features Menu * º ³ 22.00 ³ 12 ³ 05 ³ 89 CURREY º Set Margins º ³ 35.00 ³ 19 ³ 05 ³ 89 º Set Page Length º ³ ³ ³ ³ º Set Condensed Print Off º ³ ³ ³ ³ º Start Printing (LPT1) º ³ ³ ³ ³ º Send Query to File º ³ ³ ³ ³ º Return to qBase º ³ ³ ³ ³ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ Query#/Total# = 9/700 F7 - Print Report ENTER - Edit ESC - Return F3 - Save Query (.QBA) F4 - Save Query (.DBF) F5 - Data Replace If printing is requested, the printer should be set on for continuous feed. The screen will remain in the inquiry mode of qBase. This program is designed to be formatted by the user. Custom formats should be designed to fit with in the media and printer limitations. Margins, page lengths, and condense printing can be coordinated together to present the optimal report output. qBase has been developed and tested on conventional dot matrix and laser printers with consistent results. Printing formats have been designed to be "best fit", meaning, to get the most information in the least space with the best appearance. Print to File 'Send Query to File' menu selection provides the means to copy the query to an ASCII file in either DELIMITED format or clean text. Upon request, the delimited symbol is user generated along with new .TXT file name. Using the ASCII Text version, the query report can be save for later printing with the limitation of 512 characters per line (wrap occurs after this length). Save Query Parameters F3 - Save-a-Query saves the user from rekeying the same query over and over again. After selecting file(s), REPORT fields, SORT fields, CRITERIA fields, and any of the other parameters in a complex query, the user may save the query by pressing the F3 key. A window will appear in the center of the view table asking for a the name of the new {filename}.QBA file. Enter the eight or less characters, hit enter, and a new file will be created with a .QBA extension. To invoke this query in the future, have qBase in your DOS path or in the same directory as the .QBA file with the subject .DBF file(s), and key the command: C:\>qbase {filename}. The menus will be automagically be bypassed, and the data viewing screen will appear as though the menus were manually marked. Once in this view, the user can access qBase normally to print, edit, or return to the menus. Save Query Results to a .DBF File This feature will allow the qBase user to save the resulting query records in .DBF file format. If two datafiles are used, only the second or base file will be copies to the new file. All fields of the 'base' file will be transferred to the new file. Press F4, and a window will appear in the center of the screen requesting the new name of the file. Key in the new name and qBase will check for an existing .DBF file and if so, it will allow the user to continue with an overwrite, or select a new name. To return from the inquiry record screen, hit the ESC key and it will return qBase back to the Criteria Selection Menu (Figure 4). From this point, the user may start over with a different criteria selection, or use the cursor keys switching between the other menus up to the initial menu. 4. MULTIPLE DATA FILES qBase Version 1.21 has added the relational database feature giving the user the choice of one or two .DBFs to be used in the query. After the first .DBF file has been selected, a window will appear requesting an additional file to be used. If yes given, the first selected file will disappear from the list in depicted in Figure 1 and appear in the lower left corner as the first data file. The list reappear with a light bar and the second .DBF is available for selection. Select the second .DBF and a green window will appear with a list of common field of the two .DBF files. Or if there is no fields that are shared in common with either of the two data files, then a message will appear to that effect and return to the starting screen. With the green window, select the desired common field to be the 'KEY' field for the query. In most cases, there will only be one field available for the key, but the qBase has given the user the choice of any common field for the key. Figure 10 and Figure 11 give examples of two related files with 'Salesman' as the only common field: FIGURE 10: ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º SALESMAN.DBF Records are Not Sorted º º No Criteria Selected, All Records Are Displayed º º º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ SALESMAN DISTRICT PRODUCT COMMISSION ÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ RETTIG ³ WESTERN ³ ELECT ³ 10 SMITH ³ EASTERN ³ ELECT ³ 15 KOLMAN ³ NORTHERN ³ PAPER ³ 12 MATTHEWS ³ SOUTHERN ³ ELECT ³ 11 BUSH ³ MIDWEST ³ ELECT ³ 12 SILVERWOOD ³ PACIFIC ³ ELECT ³ 15 ROCKOFF ³ EUROPE ³ PAPER ³ 10 ³ ³ ³ ³ ³ ³ ³ ³ ³ Query#/Total# = 7/7 F7 - Print Report ENTER - Edit ESC - Return F3 - Save Query (.QBA) F4 - Save Query (.DBF) F5 - Data Replace FIGURE 11: ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º ORDERS.DBF Records are Not Sorted º º No Criteria Selected, All Records Are Displayed º º º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ SALEORDER ITEM SALESMAN CUSTOMER ÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ 200123 ³ CALCULATOR ³ KOLMAN ³ BOSTON GENERAL 200124 ³ DETECTOR ³ KOLMAN ³ BOSTON GENERAL 200300 ³ PAPERCUPS ³ ROCKOFF ³ THOMAS INDUSTRIES 200301 ³ VIEWER ³ ROCKOFF ³ THOMAS INDUSTRIES 200301 ³ WATERHAMMER ³ ROCKOFF ³ THOMAS INDUSTRIES 200350 ³ PAINT ³ SMITH ³ POTOMAC POWER 200350 ³ BRUSH ³ SMITH ³ POTOMAC POWER 200350 ³ LADDER ³ SMITH ³ POTOMAC POWER 200361 ³ CANOE ³ SILVERWOOD ³ L.A. EXPRESS ³ ³ ³ ³ ³ ³ Query#/Total# = 7/7 F7 - Print Report ENTER - Edit ESC - Return F3 - Save Query (.QBA) F4 - Save Query (.DBF) F5 - Data Replace Structure Query Menu When using two data files, the first file selected will be the file the relationship is set into. That is, for every occurrence of a record in the SECOND data file key field matching a data record with the same value in the FIRST data file key field, those records now have a relationship. With the example files above, SALESMAN.DBF and ORDERS.DBF have the key field SALESMAN. SALESMAN.DBF is the master file for information on the salesman, in this case, unique to each record or salesman. ORDERS.DBF is the record listing for current sales, listing the salesman for each transaction. Using qBase to establish relationships, it is advised that 'master' files be selected first, and transaction records second. The will simulate the dBase command 'SET RELATION TO SALESMAN INTO SALESMAN', with SALESMAN.DBF in first workarea and ORDERS.DBF in the second workarea. FIGURE 12: ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ºORDERS.DBF º qBase V1.21 ºSALESMAN.DBF º Copyright 1990 ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ Santa Clarita SoftWorks º Select Second º PO Box 802664 º .DBF file to Set º Santa Clarita CA 91380-2664 º Relationship into º º SALESMAN.DBF º º for Query? (Y/N) N º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ Use Cursor to Locate File, Enter to Query, ESC to Quit After selecting the first datafile, a window will prompt for second relational datafile. Answering (Y)es will return back to the first '.dbf' window minus the first data file selected. It will appear in the lower left hand of screen identified as such (Figure 12). Select the second data file and a window will appear similar to Figure 13 with a list of common fields to both datafiles. If there are no common fields, then an warning message will appear, and qBase will return to the original menu to start over again. FIGURE 13: Structure Query Menu Pick Common Key ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ºSALESMAN º ºORDERS.DBF º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ First Data File: SALESMAN.DBF Use Cursor to Locate File, Enter to Query, ESC to Quit After selection of the KEY field, qBase will construct the same schema as with a single .DBF. All fields of both the first .DBF and the second file will be listed in that order and subsequential their fields will appear in their natural order. When multiple .DBFs files are accessed, a small dot will appear to the immediate right of the field name starting at the second file. FIGURE 14: SALESMAN.DBF is Related to ORDERS.DBF and is sorted on SALESMAN Where A->DISTRICT Select Criteria Field ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º SALESMAN û º º SALEORDER º º SALESMAN º º DISTRICT û º º ITEM º º DISTRICT  º º PRODUCT û º º SALESMAN 1 º º PRODUCT º º COMMISSION û º º CUSTOMER º º COMMISSION º º SALEORDER úû º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ º SALEORDER ú º º ITEM úû º º ITEM ú º º SALESMAN úû º º SALESMAN ú º º CUSTOMER úû º º CUSTOMER ú º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍ» ºEnter - Select º º IS º º Query º º º ºF10 To Query º ºIS NOT º ºNONE for ALL º ÈÍÍÍÍÍÍͼ º<=== Sort Menu º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ FIGURE 15: SALESMAN.DBF is Related to ORDERS.DBF and is sorted on SALESMAN Where A->DISTRICT Select Criteria Field ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º SALESMAN û º º SALEORDER º º SALESMAN º º DISTRICT û º º ITEM º º DISTRICT  º º PRODUCT û º º SALESMAN 1 º º PRODUCT º º COMMISSION û º º CUSTOMER º º COMMISSION º º SALEORDER úû º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ º SALEORDER ú º º ITEM úû º º ITEM ú º º SALESMAN úû º º SALESMAN ú º º CUSTOMER úû º º CUSTOMER ú º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º Equal to Not Equal to º º Greater Than Between º º Less Than Sounds Like º Record Criteria? º Greater Than/Equal Contains º EUROPE º Less Than/Equal BLANKS or 0 º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ All Records Equal to (=) As Figure 14 shows, the two .DBF menus are similar to the single data file query. The SORT menu only reflects the second database since sorting of the first data file is meaningless during the relational query. The common fields are shown twice, the first half of the listings are of the first data file and the last half are of the second file. The user is given the choice to include the redundant KEY field chosen in the first part of the query which will always be equal. The other common fields if any may or may not contain duplicate data and are treated as any other data field. After finishing the REPORT, SORT, IS/IS NOT, and CRITERIA parameters as shown in Figure 15, press enter for the relational query results. The resulting query (Figure 16) will identify each chosen field with its parental data file. If a item occurs more than one in the key common field in the second database, the information from the first database will appear for each instance. FIGURE 16: ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º SALESMAN.DBF is Related to ORDERS.DBF and is sorted on SALESMAN º º Where A->DISTRICT is equal to EUROPE º º º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ SALESMAN-> SALESMAN-> SALESMAN-> SALESMAN-> ORDERS-> SALESMAN DISTRICT PRODUCT COMMISSION SALEORDER ÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍ ROCKOFF ³ EUROPE ³ PAPER ³ 10 ³ 200300 ROCKOFF ³ EUROPE ³ PAPER ³ 10 ³ 200301 ROCKOFF ³ EUROPE ³ PAPER ³ 10 ³ 200301 ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ Query#/Total# = 3/9 F7 - Print Report ENTER - Edit ESC - Return F3 - Save Query (.QBA) F4 - Save Query (.DBF) F5 - Data Replace FIGURE 16 (continue): ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º SALESMAN.DBF is Related to ORDERS.DBF and is sorted on SALESMAN º º Where A->DISTRICT is equal to EUROPE º º º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ ORDERS-> ORDERS-> ORDERS-> ORDERS-> SALEORDER ITEM SALESMAN CUSTOMER ÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÑÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ 200300 ³ PAPERCUPS ³ ROCKOFF ³ THOMAS INDUSTRIES 200301 ³ VIEWER ³ ROCKOFF ³ THOMAS INDUSTRIES 200301 ³ WATERHAMMER ³ ROCKOFF ³ THOMAS INDUSTRIES ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ ³ Query#/Total# = 3/9 F7 - Print Report ENTER - Edit ESC - Return F3 - Save Query (.QBA) F4 - Save Query (.DBF) F5 - Data Replace Criteria selection will work equally well in the first data file as it does with the second data file. If certain records are requested in the 'master' file, only those records will appear. 5. What is New in qBase, Version 1.21 (August 1990) a) Added Relational database queries (multiple .DBF queries) b) Added the IS/IS NOT Set Parameter c) Improved the ESCape sequence out of certain edit modes and file writes d) Added Descending Sorting along with Ascending Sorts per field e) Lengthen the possible character lengths on ASCII file names f) Enhanced the Condense print mode g) Added Display: {number of records in query} vs. {total records in file} h) Added 'Save-a-Query', one key save of a current query parameters for future use i) Added save the resulting query results to a .DBF file. Added features from qBase V1.0 (Jan 1990) to V1.1 (Jun 1990): a) Editing Mode - the user will be able to edit the data file after the query has been reported. (License Version Only) b) Global Replacements on a Query - find all records that meet a condition or set of conditions, replace that query field's data range with a new value. (License Version Only) c) Print to File - ASCII file with a .TXT extension in both Delimited and Text format. d) Access .DBF files from any directory or drive. e) Unlimited Fields Available for Reporting (V1.0 11 fields was max). f) Laser or Dot Matrix Printer Support Available g) First Menu can be by-passed by directly keying in the file name at the prompt (C:\>QBASE {filename.DBF} 6. Registration qBase V1.21 is released under the shareware concept. If you find continual use to be a habit, registration and $25.00 is requested. Santa Clarita SoftWorks attn: Robert Matthews P.O. Box 802664 Santa Clarita CA 91380-2664 Register users will receive a fully operating licensed qBase V1.21 with editing features and SCSW statements removed from printed reports. A 5.25" diskette and the printed version of this document will be included. Future upgrades will be available to licensed users with a $5.00 shipping and handling charge. This program is provided AS IS without any warranty, expressed or implied, including but not limited to fitness for a particular purpose. Santa Clarita SoftWorks hereby disclaims all warranties relating to this product, whether express or implied, including without limitation any implied warranties of merchantability or fitness for a particular purpose. Santa Clarita SoftWorks cannot and will not be liable for any special, incidental, consequential, indirect or similar damages due to loss of data or any other reason, even if Santa Clarita SoftWorks or an authorized Santa Clarita SoftWorks agent has been advised of the possibility of such damages. In no event shall the liability for any damages ever exceed the price paid for the license to use software, regardless of the form and/or extent of the claim. The user of this program bears all risk as to the quality and performance of the software. qBase is a Shareware product. As such, it is made available to the general computing public for evaluation. Users are licensed to operate qBase on their personal computers for the purpose of test and evaluation on a trial basis for a limited trial period. It is not possible to reasonably define any definitive limits of a fair and equitable time period for evaluation, therefore it is left to the user's judgement and sense of fair play as to the time required to make a decision as to its usefulness. If the user decides the program is not of sufficient merit to warrant purchase through registration with Santa Clarita SoftWorks, the program should be removed from their personal computer. Otherwise, if the program is deemed useful and is in regular use on the user's personal computer system, registration with Santa Clarita SoftWorks is required.